1 Imports System.Data.SqlClient
2 Imports System.IO
3
4 Public Class frmCourseFeePayment
5 Sub Calculate()
6 Dim num1, num2 As Double
7 num1 = CDbl(Val(txtCourseFee.Text) + Val(txtFine.Text) + Val(txtPreviousDue.Text) - Val(txtDiscount.Text))
8 num1 = Math.Round(num1, 2)
9 txtGrandTotal.Text = num1
10 num2 = Val(txtGrandTotal.Text) - Val(txtTotalPaid.Text)
11 num2 = Math.Round(num2, 2)
12 txtBalance.Text = num2
13 End Sub
14 Private Function GenerateID() As String
15 con = New SqlConnection(cs)
16 Dim value As String = "0000"
17 Try
18 ' Fetch the latest ID from the database
19 con.Open()
20 cmd = New SqlCommand("SELECT TOP 1 (CFP_ID) FROM CourseFeePayment where ClassType='Nursery' and SchoolType='English' ORDER BY CFP_ID DESC", con)
21 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
22 If rdr.HasRows Then
23 rdr.Read()
24 value = rdr.Item("CFP_ID")
25 End If
26 rdr.Close()
27 ' Increase the ID by 1
28 value += 1
29 ' Because incrementing a string with an integer removes 0's
30 ' we need to replace them. If necessary.
31 If value <= 9 Then 'Value is between 0 and 10
32 value = "000" & value
33 ElseIf value <= 99 Then 'Value is between 9 and 100
34 value = "00" & value
35 ElseIf value <= 999 Then 'Value is between 999 and 1000
36 value = "0" & value
37 End If
38 Catch ex As Exception
39 ' If an error occurs, check the connection state and close it if necessary.
40 If con.State = ConnectionState.Open Then
41 con.Close()
42 End If
43 value = "0000"
44 End Try
45 Return value
46 End Function
47 Public Sub auto()
48 Try
49 txtCFPId.Text = GenerateID()
50 Dim a As String = txtAdmissionNo.Text
51 txtFeePaymentID.Text = "ECFN-" + GenerateID() + "-" + a
52 Catch ex As Exception
53 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
54 End Try
55 End Sub
56 Private Function GenerateID1() As String
57 con = New SqlConnection(cs)
58 Dim value As String = "0000"
59 Try
60 ' Fetch the latest ID from the database
61 con.Open()
62 cmd = New SqlCommand("SELECT TOP 1 (CFP_ID) FROM CourseFeePayment where ClassType='Primary' and SchoolType='English' ORDER BY CFP_ID DESC", con)
63 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
64 If rdr.HasRows Then
65 rdr.Read()
66 value = rdr.Item("CFP_ID")
67 End If
68 rdr.Close()
69 ' Increase the ID by 1
70 value += 1
71 ' Because incrementing a string with an integer removes 0's
72 ' we need to replace them. If necessary.
73 If value <= 9 Then 'Value is between 0 and 10
74 value = "000" & value
75 ElseIf value <= 99 Then 'Value is between 9 and 100
76 value = "00" & value
77 ElseIf value <= 999 Then 'Value is between 999 and 1000
78 value = "0" & value
79 End If
80 Catch ex As Exception
81 ' If an error occurs, check the connection state and close it if necessary.
82 If con.State = ConnectionState.Open Then
83 con.Close()
84 End If
85 value = "0000"
86 End Try
87 Return value
88 End Function
89 Sub auto1()
90 Try
91 txtCFPId.Text = GenerateID1()
92 Dim a As String = txtAdmissionNo.Text
93 txtFeePaymentID.Text = "ECFP-" + GenerateID1() + "-" + a
94 Catch ex As Exception
95 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
96 End Try
97 End Sub
98 Private Function GenerateID2() As String
99 con = New SqlConnection(cs)
100 Dim value As String = "0000"
101 Try
102 ' Fetch the latest ID from the database
103 con.Open()
104 cmd = New SqlCommand("SELECT TOP 1 (CFP_ID) FROM CourseFeePayment where ClassType='Secondary' and SchoolType='English' ORDER BY CFP_ID DESC", con)
105 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
106 If rdr.HasRows Then
107 rdr.Read()
108 value = rdr.Item("CFP_ID")
109 End If
110 rdr.Close()
111 ' Increase the ID by 1
112 value += 1
113 ' Because incrementing a string with an integer removes 0's
114 ' we need to replace them. If necessary.
115 If value <= 9 Then 'Value is between 0 and 10
116 value = "000" & value
117 ElseIf value <= 99 Then 'Value is between 9 and 100
118 value = "00" & value
119 ElseIf value <= 999 Then 'Value is between 999 and 1000
120 value = "0" & value
121 End If
122 Catch ex As Exception
123 ' If an error occurs, check the connection state and close it if necessary.
124 If con.State = ConnectionState.Open Then
125 con.Close()
126 End If
127 value = "0000"
128 End Try
129 Return value
130 End Function
131 Sub auto2()
132 Try
133 txtCFPId.Text = GenerateID2()
134 Dim a As String = txtAdmissionNo.Text
135 txtFeePaymentID.Text = "ECFS-" + GenerateID2() + "-" + a
136 Catch ex As Exception
137 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
138 End Try
139 End Sub
140 Private Function GenerateID3() As String
141 con = New SqlConnection(cs)
142 Dim value As String = "0000"
143 Try
144 ' Fetch the latest ID from the database
145 con.Open()
146 cmd = New SqlCommand("SELECT TOP 1 (CFP_ID) FROM CourseFeePayment where ClassType='Higher Secondary' and SchoolType='English' ORDER BY CFP_ID DESC", con)
147 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
148 If rdr.HasRows Then
149 rdr.Read()
150 value = rdr.Item("CFP_ID")
151 End If
152 rdr.Close()
153 ' Increase the ID by 1
154 value += 1
155 ' Because incrementing a string with an integer removes 0's
156 ' we need to replace them. If necessary.
157 If value <= 9 Then 'Value is between 0 and 10
158 value = "000" & value
159 ElseIf value <= 99 Then 'Value is between 9 and 100
160 value = "00" & value
161 ElseIf value <= 999 Then 'Value is between 999 and 1000
162 value = "0" & value
163 End If
164 Catch ex As Exception
165 ' If an error occurs, check the connection state and close it if necessary.
166 If con.State = ConnectionState.Open Then
167 con.Close()
168 End If
169 value = "0000"
170 End Try
171 Return value
172 End Function
173 Sub auto3()
174 Try
175 txtCFPId.Text = GenerateID3()
176 Dim a As String = txtAdmissionNo.Text
177 txtFeePaymentID.Text = "ECFHS-" + GenerateID3() + "-" + a
178 Catch ex As Exception
179 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
180 End Try
181 End Sub
182 Private Function GenerateID4() As String
183 con = New SqlConnection(cs)
184 Dim value As String = "0000"
185 Try
186 ' Fetch the latest ID from the database
187 con.Open()
188 cmd = New SqlCommand("SELECT TOP 1 (CFP_ID) FROM CourseFeePayment where ClassType='Nursery' and SchoolType='Gujarati' ORDER BY CFP_ID DESC", con)
189 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
190 If rdr.HasRows Then
191 rdr.Read()
192 value = rdr.Item("CFP_ID")
193 End If
194 rdr.Close()
195 ' Increase the ID by 1
196 value += 1
197 ' Because incrementing a string with an integer removes 0's
198 ' we need to replace them. If necessary.
199 If value <= 9 Then 'Value is between 0 and 10
200 value = "000" & value
201 ElseIf value <= 99 Then 'Value is between 9 and 100
202 value = "00" & value
203 ElseIf value <= 999 Then 'Value is between 999 and 1000
204 value = "0" & value
205 End If
206 Catch ex As Exception
207 ' If an error occurs, check the connection state and close it if necessary.
208 If con.State = ConnectionState.Open Then
209 con.Close()
210 End If
211 value = "0000"
212 End Try
213 Return value
214 End Function
215 Sub auto4()
216 Try
217 txtCFPId.Text = GenerateID4()
218 Dim a As String = txtAdmissionNo.Text
219 txtFeePaymentID.Text = "GCFN-" + GenerateID4() + "-" + a
220 Catch ex As Exception
221 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
222 End Try
223 End Sub
224 Private Function GenerateID5() As String
225 con = New SqlConnection(cs)
226 Dim value As String = "0000"
227 Try
228 ' Fetch the latest ID from the database
229 con.Open()
230 cmd = New SqlCommand("SELECT TOP 1 (CFP_ID) FROM CourseFeePayment where ClassType='Primary' and SchoolType='Gujarati' ORDER BY CFP_ID DESC", con)
231 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
232 If rdr.HasRows Then
233 rdr.Read()
234 value = rdr.Item("CFP_ID")
235 End If
236 rdr.Close()
237 ' Increase the ID by 1
238 value += 1
239 ' Because incrementing a string with an integer removes 0's
240 ' we need to replace them. If necessary.
241 If value <= 9 Then 'Value is between 0 and 10
242 value = "000" & value
243 ElseIf value <= 99 Then 'Value is between 9 and 100
244 value = "00" & value
245 ElseIf value <= 999 Then 'Value is between 999 and 1000
246 value = "0" & value
247 End If
248 Catch ex As Exception
249 ' If an error occurs, check the connection state and close it if necessary.
250 If con.State = ConnectionState.Open Then
251 con.Close()
252 End If
253 value = "0000"
254 End Try
255 Return value
256 End Function
257 Sub auto5()
258 Try
259 txtCFPId.Text = GenerateID5()
260 Dim a As String = txtAdmissionNo.Text
261 txtFeePaymentID.Text = "GCFP-" + GenerateID5() + "-" + a
262 Catch ex As Exception
263 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
264 End Try
265 End Sub
266 Private Function GenerateID6() As String
267 con = New SqlConnection(cs)
268 Dim value As String = "0000"
269 Try
270 ' Fetch the latest ID from the database
271 con.Open()
272 cmd = New SqlCommand("SELECT TOP 1 (CFP_ID) FROM CourseFeePayment where ClassType='Secondary' and SchoolType='Gujarati' ORDER BY CFP_ID DESC", con)
273 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
274 If rdr.HasRows Then
275 rdr.Read()
276 value = rdr.Item("CFP_ID")
277 End If
278 rdr.Close()
279 ' Increase the ID by 1
280 value += 1
281 ' Because incrementing a string with an integer removes 0's
282 ' we need to replace them. If necessary.
283 If value <= 9 Then 'Value is between 0 and 10
284 value = "000" & value
285 ElseIf value <= 99 Then 'Value is between 9 and 100
286 value = "00" & value
287 ElseIf value <= 999 Then 'Value is between 999 and 1000
288 value = "0" & value
289 End If
290 Catch ex As Exception
291 ' If an error occurs, check the connection state and close it if necessary.
292 If con.State = ConnectionState.Open Then
293 con.Close()
294 End If
295 value = "0000"
296 End Try
297 Return value
298 End Function
299 Sub auto6()
300 Try
301 txtCFPId.Text = GenerateID6()
302 Dim a As String = txtAdmissionNo.Text
303 txtFeePaymentID.Text = "GCFS-" + GenerateID6() + "-" + a
304 Catch ex As Exception
305 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
306 End Try
307 End Sub
308 Private Function GenerateID7() As String
309 con = New SqlConnection(cs)
310 Dim value As String = "0000"
311 Try
312 ' Fetch the latest ID from the database
313 con.Open()
314 cmd = New SqlCommand("SELECT TOP 1 (CFP_ID) FROM CourseFeePayment where ClassType='Higher Secondary' and SchoolType='Gujarati' ORDER BY CFP_ID DESC", con)
315 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
316 If rdr.HasRows Then
317 rdr.Read()
318 value = rdr.Item("CFP_ID")
319 End If
320 rdr.Close()
321 ' Increase the ID by 1
322 value += 1
323 ' Because incrementing a string with an integer removes 0's
324 ' we need to replace them. If necessary.
325 If value <= 9 Then 'Value is between 0 and 10
326 value = "000" & value
327 ElseIf value <= 99 Then 'Value is between 9 and 100
328 value = "00" & value
329 ElseIf value <= 999 Then 'Value is between 999 and 1000
330 value = "0" & value
331 End If
332 Catch ex As Exception
333 ' If an error occurs, check the connection state and close it if necessary.
334 If con.State = ConnectionState.Open Then
335 con.Close()
336 End If
337 value = "0000"
338 End Try
339 Return value
340 End Function
341 Sub auto7()
342 Try
343 txtCFPId.Text = GenerateID7()
344 Dim a As String = txtAdmissionNo.Text
345 txtFeePaymentID.Text = "GCFHS-" + GenerateID7() + "-" + a
346 Catch ex As Exception
347 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
348 End Try
349 End Sub
350 Sub Reset()
351 txtAdmissionNo.Text = ""
352 txtCourseFee.Text = ""
353 txtPaymentModeDetails.Text = ""
354 txtBalance.Text = ""
355 txtClass.Text = ""
356 txtDiscount.Text = ""
357 txtDiscountPer.Text = ""
358 txtEnrollmentNo.Text = ""
359 txtFine.Text = ""
360 txtGrandTotal.Text = ""
361 txtPreviousDue.Text = ""
362 txtSchoolName.Text = ""
363 txtSection.Text = ""
364 txtSession.Text = ""
365 txtStudentName.Text = ""
366 txtTotalPaid.Text = ""
367 txtClassType.Text = ""
368 txtSchoolType.Text = ""
369 cmbPaymentMode.SelectedIndex = -1
370 cmbSemester.SelectedIndex = -1
371 dtpPaymentDate.Text = Now
372 dgw.Rows.Clear()
373 btnSave.Enabled = True
374 btnUpdate.Enabled = False
375 btnDelete.Enabled = False
376 btnPrint.Enabled = False
377 Button2.Enabled = True
378 dtpPaymentDate.Enabled = True
379 End Sub
380 Sub FillData()
381 Try
382 con = New SqlConnection(cs)
383 con.Open()
384 cmd = con.CreateCommand()
385 cmd.CommandText = "SELECT Type FROM ClassType,Class where ClassType.Type=Class.ClassType and Classname=@d1"
386 cmd.Parameters.AddWithValue("@d1", txtClass.Text)
387 rdr = cmd.ExecuteReader()
388 If rdr.Read() Then
389 txtClassType.Text = rdr.GetValue(0).ToString().Trim()
390 End If
391 If (rdr IsNot Nothing) Then
392 rdr.Close()
393 End If
394 If con.State = ConnectionState.Open Then
395 con.Close()
396 End If
397 con = New SqlConnection(cs)
398 con.Open()
399 cmd = con.CreateCommand()
400 cmd.CommandText = "SELECT Type FROM SchoolType,SchoolInfo where SchoolType.Type=SchoolInfo.SchoolType and SchoolName=@d1"
401 cmd.Parameters.AddWithValue("@d1", txtSchoolName.Text)
402 rdr = cmd.ExecuteReader()
403 If rdr.Read() Then
404 txtSchoolType.Text = rdr.GetValue(0).ToString().Trim()
405 End If
406 If (rdr IsNot Nothing) Then
407 rdr.Close()
408 End If
409 If con.State = ConnectionState.Open Then
410 con.Close()
411 End If
412 Catch ex As Exception
413 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
414 End Try
415 End Sub
416 Sub Fill()
417 Try
418 con = New SqlConnection(cs)
419 con.Open()
420 cmd = con.CreateCommand()
421 cmd.CommandText = "SELECT Discount from Discount where AdmissionNo=@d1 and FeeType='Class'"
422 cmd.Parameters.AddWithValue("@d1", txtAdmissionNo.Text)
423 rdr = cmd.ExecuteReader()
424 If rdr.Read() Then
425 txtDiscountPer.Text = rdr.GetValue(0)
426 Dim num As Double
427 num = CDbl((Val(txtCourseFee.Text) * Val(txtDiscountPer.Text)) / 100)
428 num = Math.Round(num, 2)
429 txtDiscount.Text = num
430 End If
431 If (rdr IsNot Nothing) Then
432 rdr.Close()
433 End If
434 If con.State = ConnectionState.Open Then
435 con.Close()
436 End If
437
438 Calculate()
439 Catch ex As Exception
440 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
441 End Try
442 End Sub
443 Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
444 frmStudentRecord.lblSet.Text = "Course Fee Payment"
445 frmStudentRecord.Reset()
446 frmStudentRecord.ShowDialog()
447 End Sub
448
449 Private Sub btnGetFeeList_Click(sender As System.Object, e As System.EventArgs) Handles btnGetFeeList.Click
450 Try
451 If txtAdmissionNo.Text = "" Then
452 MessageBox.Show("Please retrieve student info", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
453 txtAdmissionNo.Focus()
454 Return
455 End If
456 If cmbSemester.Text = "" Then
457 MessageBox.Show("Please select semester", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
458 cmbSemester.Focus()
459 Return
460 End If
461 con = New SqlConnection(cs)
462 con.Open()
463 cmd = New SqlCommand("SELECT RTRIM(FeeName),RTRIM(Fee) from CourseFee,SchoolInfo where CourseFee.SchoolID=SchoolInfo.S_ID and CourseFee.Class=@d1 and Semester=@d2 and SchoolName=@d3 order by FeeName", con)
464 cmd.Parameters.AddWithValue("@d1", txtClass.Text)
465 cmd.Parameters.AddWithValue("@d2", cmbSemester.Text)
466 cmd.Parameters.AddWithValue("@d3", txtSchoolName.Text)
467 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
468 dgw.Rows.Clear()
469 While (rdr.Read() = True)
470 dgw.Rows.Add(rdr(0), rdr(1))
471 End While
472 Dim sum As Double = 0
473 For Each r As DataGridViewRow In Me.dgw.Rows
474 sum = sum + r.Cells(1).Value
475 Next
476 sum = Math.Round(sum, 2)
477 txtCourseFee.Text = sum
478 con.Close()
479 con = New SqlConnection(cs)
480 con.Open()
481 cmd = con.CreateCommand()
482 cmd.CommandText = "SELECT Sum(PaymentDue-PreviousDue) from CourseFeePayment where AdmissionNo=@d1 group by AdmissionNo"
483 cmd.Parameters.AddWithValue("@d1", txtAdmissionNo.Text)
484 rdr = cmd.ExecuteReader()
485 If rdr.Read() Then
486 txtPreviousDue.Text = rdr.GetValue(0)
487 Else
488 txtPreviousDue.Text = 0
489 End If
490 If (rdr IsNot Nothing) Then
491 rdr.Close()
492 End If
493 If con.State = ConnectionState.Open Then
494 con.Close()
495 End If
496 Fill()
497 Catch ex As Exception
498 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
499 End Try
500 End Sub
501
502 Private Sub btnNew_Click(sender As System.Object, e As System.EventArgs) Handles btnNew.Click
503 Reset()
504 Reset()
505 End Sub
506
507 Private Sub btnSave_Click(sender As System.Object, e As System.EventArgs) Handles btnSave.Click
508 If Len(Trim(txtAdmissionNo.Text)) = 0 Then
509 MessageBox.Show("Please retrieve student info", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
510 txtAdmissionNo.Focus()
511 Exit Sub
512 End If
513 If dgw.Rows.Count = 0 Then
514 MessageBox.Show("Please add fee info in a datagrid", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
515 Exit Sub
516 End If
517 If Len(Trim(txtFine.Text)) = 0 Then
518 MessageBox.Show("Please enter fine", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
519 txtFine.Focus()
520 Exit Sub
521 End If
522 If Len(Trim(cmbPaymentMode.Text)) = 0 Then
523 MessageBox.Show("Please select Payment Mode", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
524 cmbPaymentMode.Focus()
525 Exit Sub
526 End If
527 If Len(Trim(txtTotalPaid.Text)) = 0 Then
528 MessageBox.Show("Please enter total paid", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
529 txtTotalPaid.Focus()
530 Exit Sub
531 End If
532 If Val(txtBalance.Text) < 0 Then
533 MessageBox.Show("Balance is not possible less than zero", "Input error", MessageBoxButtons.OK, MessageBoxIcon.Error)
534 Exit Sub
535 End If
536 Try
537 con = New SqlConnection(cs)
538 con.Open()
539 Dim ct As String = "select Session,AdmissionNo,Semester from CourseFeePayment where Session=@d1 and AdmissionNo=@d2 and Semester=@d3"
540 cmd = New SqlCommand(ct)
541 cmd.Connection = con
542 cmd.Parameters.AddWithValue("@d1", txtSession.Text)
543 cmd.Parameters.AddWithValue("@d2", txtAdmissionNo.Text)
544 cmd.Parameters.AddWithValue("@d3", cmbSemester.Text)
545 rdr = cmd.ExecuteReader()
546 If rdr.Read Then
547 MessageBox.Show("Already paid", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
548 If Not rdr Is Nothing Then
549 rdr.Close()
550 End If
551 Exit Sub
552 End If
553 If txtClassType.Text = "Nursery" And txtSchoolType.Text = "English" Then
554 auto()
555 End If
556 If txtClassType.Text = "Primary" And txtSchoolType.Text = "English" Then
557 auto1()
558 End If
559 If txtClassType.Text = "Secondary" And txtSchoolType.Text = "English" Then
560 auto2()
561 End If
562 If txtClassType.Text = "Higher Secondary" And txtSchoolType.Text = "English" Then
563 auto3()
564 End If
565 If txtClassType.Text = "Nursery" And txtSchoolType.Text = "Gujarati" Then
566 auto4()
567 End If
568 If txtClassType.Text = "Primary" And txtSchoolType.Text = "Gujarati" Then
569 auto5()
570 End If
571 If txtClassType.Text = "Secondary" And txtSchoolType.Text = "Gujarati" Then
572 auto6()
573 End If
574 If txtClassType.Text = "Higher Secondary" And txtSchoolType.Text = "Gujarati" Then
575 auto7()
576 End If
577 con = New SqlConnection(cs)
578 con.Open()
579 Dim cb As String = "insert into CourseFeePayment(CFP_ID, PaymentID, AdmissionNo, Session, Semester,TotalFee, DiscountPer, DiscountAmt, PreviousDue, Fine, GrandTotal, TotalPaid, ModeOfPayment, PaymentModeDetails, PaymentDate, PaymentDue, ClassType,SchoolType,Class,Section) VALUES (@d1,@d2,@d3,@d4,@d5,@d6,@d7,@d8,@d9,@d10,@d11,@d12,@d13,@d14,@d15,@d16,@d17,@d18,@d19,@d20)"
580 cmd = New SqlCommand(cb)
581 cmd.Connection = con
582 cmd.Parameters.AddWithValue("@d1", txtCFPId.Text)
583 cmd.Parameters.AddWithValue("@d2", txtFeePaymentID.Text)
584 cmd.Parameters.AddWithValue("@d3", txtAdmissionNo.Text)
585 cmd.Parameters.AddWithValue("@d4", txtSession.Text)
586 cmd.Parameters.AddWithValue("@d5", cmbSemester.Text)
587 cmd.Parameters.AddWithValue("@d6", txtCourseFee.Text)
588 cmd.Parameters.AddWithValue("@d7", txtDiscountPer.Text)
589 cmd.Parameters.AddWithValue("@d8", txtDiscount.Text)
590 cmd.Parameters.AddWithValue("@d9", txtPreviousDue.Text)
591 cmd.Parameters.AddWithValue("@d10", txtFine.Text)
592 cmd.Parameters.AddWithValue("@d11", txtGrandTotal.Text)
593 cmd.Parameters.AddWithValue("@d12", txtTotalPaid.Text)
594 cmd.Parameters.AddWithValue("@d13", cmbPaymentMode.Text)
595 cmd.Parameters.AddWithValue("@d14", txtPaymentModeDetails.Text)
596 cmd.Parameters.AddWithValue("@d15", dtpPaymentDate.Value)
597 cmd.Parameters.AddWithValue("@d16", txtBalance.Text)
598 cmd.Parameters.AddWithValue("@d17", txtClassType.Text)
599 cmd.Parameters.AddWithValue("@d18", txtSchoolType.Text)
600 cmd.Parameters.AddWithValue("@d19", txtClass.Text)
601 cmd.Parameters.AddWithValue("@d20", txtSection.Text)
602 cmd.ExecuteNonQuery()
603 con.Close()
604 con = New SqlConnection(cs)
605 con.Open()
606 cmd = con.CreateCommand()
607 cmd.CommandText = "SELECT ID FROM CourseFeePayment where PaymentID=@d1"
608 cmd.Parameters.AddWithValue("@d1", txtFeePaymentID.Text)
609 rdr = cmd.ExecuteReader()
610 If rdr.Read() Then
611 txtID.Text = rdr.GetValue(0)
612 End If
613 If (rdr IsNot Nothing) Then
614 rdr.Close()
615 End If
616 If con.State = ConnectionState.Open Then
617 con.Close()
618 End If
619 con = New SqlConnection(cs)
620 con.Open()
621 Dim cb1 As String = "insert into CourseFeePayment_Join(C_PaymentID, FeeName, Fee) VALUES (" & txtID.Text & ",@d1,@d2)"
622 cmd = New SqlCommand(cb1)
623 cmd.Connection = con
624 ' Prepare command for repeated execution
625 cmd.Prepare()
626 ' Data to be inserted
627 For Each row As DataGridViewRow In dgw.Rows
628 If Not row.IsNewRow Then
629 cmd.Parameters.AddWithValue("@d1", row.Cells(0).Value)
630 cmd.Parameters.AddWithValue("@d2", row.Cells(1).Value)
631 cmd.ExecuteNonQuery()
632 cmd.Parameters.Clear()
633 End If
634 Next
635 con.Close()
636 Dim st As String = "added the new course fee payment entry having payment id '" & txtFeePaymentID.Text & "'"
637 LogFunc(lblUser.Text, st)
638 MessageBox.Show("Successfully paid", "Fee", MessageBoxButtons.OK, MessageBoxIcon.Information)
639 btnSave.Enabled = False
640 con.Close()
641 Print()
642 Catch ex As Exception
643 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
644 End Try
645 End Sub
646
647 Private Sub btnDelete_Click(sender As System.Object, e As System.EventArgs) Handles btnDelete.Click
648 Try
649 If MessageBox.Show("Do you really want to delete the record?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) = Windows.Forms.DialogResult.Yes Then
650 delete_records()
651 End If
652 Catch ex As Exception
653 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
654 End Try
655 End Sub
656 Private Sub delete_records()
657 Try
658 Dim RowsAffected As Integer = 0
659 con = New SqlConnection(cs)
660 con.Open()
661 Dim cq As String = "delete from CourseFeePayment where ID= " & txtID.Text & ""
662 cmd = New SqlCommand(cq)
663 cmd.Connection = con
664 RowsAffected = cmd.ExecuteNonQuery()
665 If RowsAffected > 0 Then
666 Dim st As String = "deleted the course fee payment entry having payment id '" & txtFeePaymentID.Text & "'"
667 LogFunc(lblUser.Text, st)
668 MessageBox.Show("Successfully deleted", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
669 Reset()
670 Reset()
671 Else
672 MessageBox.Show("No record found", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information)
673 Reset()
674 Reset()
675 End If
676 con.Close()
677 Catch ex As Exception
678 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
679 End Try
680 End Sub
681
682 Private Sub btnUpdate_Click(sender As System.Object, e As System.EventArgs) Handles btnUpdate.Click
683 If Len(Trim(txtAdmissionNo.Text)) = 0 Then
684 MessageBox.Show("Please retrieve student info", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
685 txtAdmissionNo.Focus()
686 Exit Sub
687 End If
688 If dgw.Rows.Count = 0 Then
689 MessageBox.Show("Please add fee info in a datagrid", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
690 Exit Sub
691 End If
692 If Len(Trim(txtFine.Text)) = 0 Then
693 MessageBox.Show("Please enter fine", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
694 txtFine.Focus()
695 Exit Sub
696 End If
697 If Len(Trim(cmbPaymentMode.Text)) = 0 Then
698 MessageBox.Show("Please select Payment Mode", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
699 cmbPaymentMode.Focus()
700 Exit Sub
701 End If
702 If Len(Trim(txtTotalPaid.Text)) = 0 Then
703 MessageBox.Show("Please enter total paid", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
704 txtTotalPaid.Focus()
705 Exit Sub
706 End If
707 If Val(txtBalance.Text) < 0 Then
708 MessageBox.Show("Balance is not possible less than zero", "Input error", MessageBoxButtons.OK, MessageBoxIcon.Error)
709 Exit Sub
710 End If
711 Try
712 'con = New SqlConnection(cs)
713 'con.Open()
714 'Dim ct As String = "select PaymentDate from CourseFeePayment where AdmissionNo=@d1"
715 'cmd = New SqlCommand(ct)
716 'cmd.Connection = con
717 'cmd.Parameters.AddWithValue("@d1", txtAdmissionNo.Text)
718 'Dim da As New SqlDataAdapter(cmd)
719 'Dim ds As DataSet = New DataSet()
720 'da.Fill(ds)
721 'If ds.Tables(0).Rows.Count > 0 Then
722 'If dtpPaymentDate.Value.Date < ds.Tables(0).Rows(0)("PaymentDate") Then
723 'MessageBox.Show("updating old record is not allowed when student has been already paid fee again", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
724 'End If
725 'Exit Sub
726 'End If
727 'con.Close()
728 con = New SqlConnection(cs)
729 con.Open()
730 Dim cb As String = "Update CourseFeePayment set CFP_ID=@d1, PaymentID=@d2, AdmissionNo=@d3, Session=@d4, Semester=@d5,TotalFee=@d6, DiscountPer=@d7, DiscountAmt=@d8, PreviousDue=@d9, Fine=@d10, GrandTotal=@d11, TotalPaid=@d12, ModeOfPayment=@d13, PaymentModeDetails=@d14, PaymentDue=@d16, ClassType=@d17,SchoolType=@d18,Class=@d19,Section=@d20 where ID= " & txtID.Text & ""
731 cmd = New SqlCommand(cb)
732 cmd.Connection = con
733 cmd.Parameters.AddWithValue("@d1", txtCFPId.Text)
734 cmd.Parameters.AddWithValue("@d2", txtFeePaymentID.Text)
735 cmd.Parameters.AddWithValue("@d3", txtAdmissionNo.Text)
736 cmd.Parameters.AddWithValue("@d4", txtSession.Text)
737 cmd.Parameters.AddWithValue("@d5", cmbSemester.Text)
738 cmd.Parameters.AddWithValue("@d6", txtCourseFee.Text)
739 cmd.Parameters.AddWithValue("@d7", txtDiscountPer.Text)
740 cmd.Parameters.AddWithValue("@d8", txtDiscount.Text)
741 cmd.Parameters.AddWithValue("@d9", txtPreviousDue.Text)
742 cmd.Parameters.AddWithValue("@d10", txtFine.Text)
743 cmd.Parameters.AddWithValue("@d11", txtGrandTotal.Text)
744 cmd.Parameters.AddWithValue("@d12", txtTotalPaid.Text)
745 cmd.Parameters.AddWithValue("@d13", cmbPaymentMode.Text)
746 cmd.Parameters.AddWithValue("@d14", txtPaymentModeDetails.Text)
747 cmd.Parameters.AddWithValue("@d16", txtBalance.Text)
748 cmd.Parameters.AddWithValue("@d17", txtClassType.Text)
749 cmd.Parameters.AddWithValue("@d18", txtSchoolType.Text)
750 cmd.Parameters.AddWithValue("@d19", txtClass.Text)
751 cmd.Parameters.AddWithValue("@d20", txtSection.Text)
752 cmd.ExecuteNonQuery()
753 con.Close()
754 con = New SqlConnection(cs)
755 con.Open()
756 Dim cq As String = "delete from CourseFeePayment_Join where C_PaymentID= " & txtID.Text & ""
757 cmd = New SqlCommand(cq)
758 cmd.Connection = con
759 cmd.ExecuteNonQuery()
760 con.Close()
761 con = New SqlConnection(cs)
762 con.Open()
763 Dim cb1 As String = "insert into CourseFeePayment_Join(C_PaymentID, Feename, Fee) VALUES (" & txtID.Text & ",@d1,@d2)"
764 cmd = New SqlCommand(cb1)
765 cmd.Connection = con
766 ' Prepare command for repeated execution
767 cmd.Prepare()
768 ' Data to be inserted
769 For Each row As DataGridViewRow In dgw.Rows
770 If Not row.IsNewRow Then
771 cmd.Parameters.AddWithValue("@d1", row.Cells(0).Value)
772 cmd.Parameters.AddWithValue("@d2", row.Cells(1).Value)
773 cmd.ExecuteNonQuery()
774 cmd.Parameters.Clear()
775 End If
776 Next
777 con.Close()
778 Dim st As String = "updated the course fee payment entry having payment id '" & txtFeePaymentID.Text & "'"
779 LogFunc(lblUser.Text, st)
780 MessageBox.Show("Successfully updated", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
781 btnUpdate.Enabled = False
782 con.Close()
783 Catch ex As Exception
784 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
785 End Try
786 End Sub
787
788 Private Sub txtFine_KeyPress(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles txtFine.KeyPress
789 Dim keyChar = e.KeyChar
790
791 If Char.IsControl(keyChar) Then
792 'Allow all control characters.
793 ElseIf Char.IsDigit(keyChar) OrElse keyChar = "."c Then
794 Dim text = Me.txtFine.Text
795 Dim selectionStart = Me.txtFine.SelectionStart
796 Dim selectionLength = Me.txtFine.SelectionLength
797
798 text = text.Substring(0, selectionStart) & keyChar & text.Substring(selectionStart + selectionLength)
799
800 If Integer.TryParse(text, New Integer) AndAlso text.Length > 16 Then
801 'Reject an integer that is longer than 16 digits.
802 e.Handled = True
803 ElseIf Double.TryParse(text, New Double) AndAlso text.IndexOf("."c) < text.Length - 3 Then
804 'Reject a real number with two many decimal places.
805 e.Handled = False
806 End If
807 Else
808 'Reject all other characters.
809 e.Handled = True
810 End If
811 End Sub
812
813 Private Sub txtTotalPaid_KeyPress(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles txtTotalPaid.KeyPress
814 Dim keyChar = e.KeyChar
815
816 If Char.IsControl(keyChar) Then
817 'Allow all control characters.
818 ElseIf Char.IsDigit(keyChar) OrElse keyChar = "."c Then
819 Dim text = Me.txtTotalPaid.Text
820 Dim selectionStart = Me.txtTotalPaid.SelectionStart
821 Dim selectionLength = Me.txtTotalPaid.SelectionLength
822
823 text = text.Substring(0, selectionStart) & keyChar & text.Substring(selectionStart + selectionLength)
824
825 If Integer.TryParse(text, New Integer) AndAlso text.Length > 16 Then
826 'Reject an integer that is longer than 16 digits.
827 e.Handled = True
828 ElseIf Double.TryParse(text, New Double) AndAlso text.IndexOf("."c) < text.Length - 3 Then
829 'Reject a real number with two many decimal places.
830 e.Handled = False
831 End If
832 Else
833 'Reject all other characters.
834 e.Handled = True
835 End If
836 End Sub
837
838 Private Sub txtTotalPaid_Validating(sender As System.Object, e As System.ComponentModel.CancelEventArgs) Handles txtTotalPaid.Validating
839 If Val(txtTotalPaid.Text) > Val(txtGrandTotal.Text) Then
840 MessageBox.Show("Total Pay can not be more than grand total", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
841 txtTotalPaid.Text = ""
842 txtTotalPaid.Focus()
843 Exit Sub
844 End If
845 End Sub
846
847 Private Sub txtFine_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtFine.TextChanged
848 Fill()
849 End Sub
850
851 Private Sub txtTotalPaid_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtTotalPaid.TextChanged
852 Fill()
853 End Sub
854
855 Private Sub btnClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
856 Me.Close()
857 End Sub
858
859 Private Sub btnGetData_Click(sender As System.Object, e As System.EventArgs) Handles btnGetData.Click
860 frmCourseFeePaymentRecord.lblSet.Text = "Course Fee Payment"
861 frmCourseFeePaymentRecord.Reset()
862 frmCourseFeePaymentRecord.ShowDialog()
863 End Sub
864 Sub Print()
865 Try
866 Cursor = Cursors.WaitCursor
867 Timer1.Enabled = True
868 Dim rpt As New rptCourseFeeReceipt 'The report you created.
869 Dim myConnection As SqlConnection
870 Dim MyCommand As New SqlCommand()
871 Dim myDA As New SqlDataAdapter()
872 Dim myDS As New DataSet 'The DataSet you created.
873 myConnection = New SqlConnection(cs)
874 MyCommand.Connection = myConnection
875 MyCommand.CommandText = "SELECT SchoolInfo.S_Id, SchoolInfo.SchoolName, SchoolInfo.Address, SchoolInfo.ContactNo, SchoolInfo.AltContactNo, SchoolInfo.FaxNo, SchoolInfo.Email, SchoolInfo.Website, SchoolInfo.Logo, SchoolInfo.RegistrationNo,SchoolInfo.DiseNo, SchoolInfo.IndexNo, SchoolInfo.EstablishedYear, Student.AdmissionNo, Student.EnrollmentNo, Student.GRNo, Student.UID, Student.StudentName,Student.FatherName, Student.MotherName, Student.FatherCN, Student.PermanentAddress, Student.TemporaryAddress, Student.EmailID, Student.DOB, Student.Gender,Student.AdmissionDate, Student.Session, Student.Caste, Student.Religion, Student.SectionID, Student.Photo, Student.Nationality, Student.SchoolID, Student.LastSchoolAttended, Student.Result,Student.PassPercentage, CourseFeePayment.Id, CourseFeePayment.CFP_ID, CourseFeePayment.PaymentID,CourseFeePayment.Semester, CourseFeePayment.TotalFee, CourseFeePayment.DiscountPer, CourseFeePayment.DiscountAmt, CourseFeePayment.PreviousDue, CourseFeePayment.Fine,CourseFeePayment.GrandTotal, CourseFeePayment.TotalPaid, CourseFeePayment.ModeOfPayment, CourseFeePayment.PaymentModeDetails, CourseFeePayment.PaymentDate,CourseFeePayment.PaymentDue, CourseFeePayment.ClassType, CourseFeePayment.Class , CourseFeePayment.Section, CourseFeePayment_Join.CFPJ_Id,CourseFeePayment_Join.C_PaymentID, CourseFeePayment_Join.FeeName, CourseFeePayment_Join.Fee FROM SchoolInfo INNER JOIN Student ON SchoolInfo.S_Id = Student.SchoolID INNER JOIN CourseFeePayment ON Student.AdmissionNo = CourseFeePayment.AdmissionNo INNER JOIN CourseFeePayment_Join ON CourseFeePayment.Id = CourseFeePayment_Join.C_PaymentID where CourseFeePayment.PaymentID='" & txtFeePaymentID.Text & "'"
876 MyCommand.CommandType = CommandType.Text
877 myDA.SelectCommand = MyCommand
878 myDA.Fill(myDS, "SchoolInfo")
879 myDA.Fill(myDS, "Student")
880 myDA.Fill(myDS, "CourseFeePayment")
881 myDA.Fill(myDS, "CourseFeePayment_Join")
882 rpt.SetDataSource(myDS)
883 frmReport.CrystalReportViewer1.ReportSource = rpt
884 frmReport.ShowDialog()
885 Catch ex As Exception
886 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
887 End Try
888 End Sub
889 Private Sub frmCourseFeePayment_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
890
891 End Sub
892
893 Private Sub btnPrint_Click(sender As System.Object, e As System.EventArgs) Handles btnPrint.Click
894 Print()
895 End Sub
896
897 Private Sub Timer1_Tick(sender As System.Object, e As System.EventArgs) Handles Timer1.Tick
898 Cursor = Cursors.Default
899 Timer1.Enabled = False
900 End Sub
901 End Class
